﻿using Dapper;
using System.Data.SqlClient;
using WebApplication1.Entity;
using yunbo.Model.SSM;

namespace yunbo.Repositorys.SSMRepositorys
{
    public class RoleRepository
    {
        public string data = "Data Source=121.37.93.71;Initial Catalog=FanXingMES;User ID=sa;Password=geili@2025;";
        public FYRole GetFYRole(int PageNumber, int PageSize)
        {

            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"SELECT * FROM [dbo].[Role] where  is_deleted=1\r\nORDER BY  [role_id] \r\nOFFSET ({PageNumber}-1)*{PageSize} ROWS\r\nFETCH NEXT {PageSize} ROWS ONLY;";
                List<Role> role = connection.Query<Role>(sql).ToList();
                string sql1 = $"SELECT COUNT(*) FROM [dbo].[Role] where is_deleted=1";
                int zon = connection.Query<int>(sql1).FirstOrDefault();
                FYRole fYRole = new FYRole()
                {
                    role = role,
                    zon = zon,
                };
                return fYRole;
            }
        }
        public List<Role> GetRole()
        {

            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"SELECT * FROM [dbo].[Role] where role_status=1 and is_deleted=1";
                List<Role> role = connection.Query<Role>(sql).ToList();
                return role;
            }
        }
        public int PostRole(Role role)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"insert into [dbo].[role] ([role_name],[role_status],[role_notes],[role_create_by],[role_create_time],[is_deleted]) values ('{role.role_name}',{role.role_status},'{role.role_notes}',{role.role_create_by},'{role.role_create_time}',1)";
                int rs = connection.Execute(sql);
                return rs;
            }
        }
        public int UpdateRole(Role role)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"update [dbo].[role] set [role_name]='{role.role_name}',[role_notes]='{role.role_notes}',[role_update_by]={role.role_update_by},[role_update_time]='{role.role_update_time}' where role_id={role.role_id}";
                int rs = connection.Execute(sql);
                return rs;
            }
        }
        public int DeleteRole(int id)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"update [dbo].[role] set is_deleted=0 WHERE role_id={id}";
                int rs = connection.Execute(sql);
                return rs;
            }
        }
        public FYRole GetFYMHRole(int PageNumber, int PageSize, int? state, string? uname)
        {

            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"SELECT * FROM [dbo].[role] WHERE role_status like '%{state}%' and role_name like '%{uname}%' and is_deleted=1\r\nORDER BY  [role_id] \r\nOFFSET ({PageNumber}-1)*{PageSize} ROWS\r\nFETCH NEXT {PageSize} ROWS ONLY;";
                List<Role> role = connection.Query<Role>(sql).ToList();
                string sql1 = $"SELECT COUNT(*) FROM [dbo].[role] WHERE role_status like '%{state}%' and role_name like '%{uname}%' and is_deleted=1";
                int zon = connection.Query<int>(sql1).FirstOrDefault();
                FYRole fYRole = new FYRole()
                {
                    role = role,
                    zon = zon,
                };
                return fYRole;
            }
        }
        public int PostUser_Role(string code,int id)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"insert into [dbo].[user_role] values('{code}',{id})";
                int rs = connection.Execute(sql);
                return rs;
            }
        }
        public int UpdateUser_Role(string code, int id)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"update [dbo].[user_role] set role_id={id} where user_code='{code}'";
                int rs = connection.Execute(sql);
                return rs;
            }
        }
        public int GetUser_Role(string code)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"select role_id from [dbo].[user_role] where user_code='{code}'";
                int rs = connection.Query<int>(sql).FirstOrDefault();
                return rs;
            }
        }
        public int UpdateRoleZT(int id, int state)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"update [dbo].[Role] set role_status={state} where role_id={id}";
                int rs = connection.Execute(sql);
                return rs;
            }
        }
    }
}
